﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data.SqlClient;
using IDAL;


namespace SQLServerDAL
{
    public class UserDAL : IDAL.IUserDAL
    {
        //新增用户
        public Boolean AddUser(UserDetail user)
        {
            Boolean flag = true;
            SQLServerDAL dal = new SQLServerDAL();
            dal.CommandText = "insert into UserDetail_Tab(user_id,userDetail_name,userDetail_email,userDetail_sex,userDetail_birth,userDetail_phone,userDetail_nation,userDetail_province,"+
                "userDetail_city,userDetail_regDate,userDetail_identity,userDetail_state,userDetail_memo,userDetail_safeQue,userDetail_safeAns,userDetail_pwd ) values(20,'" + user.UserDetail_name + "','" + user.UserDetail_email + "','" + user.UserDetail_sex + "','" +
                user.UserDetail_birth+"','"+user.UserDetail_phone+"','"+user.UserDetail_nation+"','"+user.UserDetail_province+"','"+user.UserDetail_city+"','"+user.UserDetail_regDate+"','',"
                +user.UserDetail_state+",'"+user.UserDetail_memo+"','"+user.UserDetail_safeQue+"','"+user.UserDetail_safeAns+"','"+user.UserDetail_pwd+"');";
            dal.ExecuteNonQuery();
            return flag;
        }

        public Boolean DelUserById(int id)
        {
            Boolean flag = true;
            SQLServerDAL dal = new SQLServerDAL();
            dal.CommandText = "delete from UserDetail_Tab where user_id =" + id;
            dal.ExecuteNonQuery();
            return flag;
        }

        public Boolean UpdUser(int id,UserDetail user)
        {
            Boolean flag = true;
            SQLServerDAL dal = new SQLServerDAL();
            dal.CommandText = "update UserDetail_Tab set userDetail_name = '" + user.UserDetail_name + "' , userDetail_email ='" + user.UserDetail_email + "',userDetail_sex = '" + user.UserDetail_sex + "',userDetail_birth = '" + user.UserDetail_birth + "',userDetail_phone ='"
                + user.UserDetail_phone + "',userDetail_nation = '" + user.UserDetail_nation + "',userDetail_province = '" + user.UserDetail_province + "',userDetail_city ='" + user.UserDetail_city + "',userDetail_regDate ='"+user.UserDetail_regDate+"',userDetail_identity ='',userDetail_state = "
                + user.UserDetail_state + ",userDetail_memo ='" + user.UserDetail_memo + "',userDetail_safeQue = '" + user.UserDetail_safeQue + "',userDetail_safeAns = '" + user.UserDetail_safeAns + "',userDetail_pwd = '" + user.UserDetail_pwd+"' where user_id = "+id;
            dal.ExecuteNonQuery();
            return flag;
        }

        public List<UserDetail> selectUserByName(string username)
        {
            List<UserDetail> arrList = new List<UserDetail>();
            SQLServerDAL dal = new SQLServerDAL();
            if (username != null && username.Length > 0)
            {
                dal.CommandText = "select * from UserDetail_Tab where userDetail_name  LIKE '%" + username + "%'";
            }
            else
            {
                dal.CommandText = "select * from UserDetail_Tab ";
            }
            dal.ExecuteReader();
            while (dal.Reader.Read())
            {
                UserDetail tempUser = new UserDetail();
                FillUserWithReader(tempUser, dal.Reader);
                arrList.Add(tempUser);
            }
            return arrList;
        }

        public UserDetail selectUserById(int id)
        {
            UserDetail user = new UserDetail();
            SQLServerDAL dal = new SQLServerDAL();
            if (id != 0)
            {
                dal.CommandText = "select * from UserDetail_tab where user_id = " + id;
                dal.ExecuteReader();
                while (dal.Reader.Read())
                {
                    FillUserWithReader(user, dal.Reader);
                }
            }
            else
            {
                user = null;
            }
            return user;

        }

        public void FillUserWithReader(UserDetail user, SqlDataReader reader)
        {
            user.User_id = reader.GetInt32(0);
            user.UserDetail_name = reader.GetString(1);
            user.UserDetail_email = reader.GetString(2);
            user.UserDetail_realName = reader.IsDBNull(3) ? null : reader.GetString(3); 
            user.UserDetail_sex = reader.GetBoolean(4);
            user.UserDetail_birth = reader.IsDBNull(5) ? null : reader.GetString(5);
            user.UserDetail_phone = reader.IsDBNull(6) ? null : reader.GetString(6);
            user.UserDetail_nation = reader.IsDBNull(7) ? null : reader.GetString(7);
            user.UserDetail_province = reader.IsDBNull(8) ? null : reader.GetString(8);
            user.UserDetail_city = reader.IsDBNull(9) ? null : reader.GetString(9);
            user.UserDetail_regDate = reader.GetString(10);
            user.UserDetail_identity = reader.IsDBNull(11) ? null : reader.GetString(11);
            user.UserDetail_memo = reader.IsDBNull(12) ? null : reader.GetString(12);
            user.UserDetail_safeQue = reader.GetString(13);
            user.UserDetail_safeAns = reader.GetString(14);
            user.UserDetail_state = reader.GetInt32(15);
            user.UserDetail_pwd = reader.GetString(16);

        }

    }

    
}
